/*
distributor_sales_report.rpt
This Procedure Shows the Distributor Shipment Information.
EXEC sp_distributor_ship_details_report 0,'0','0','0','SalesReport','XLREPORT'
*/
ALTER PROC sp_distributor_ship_details_report

@DISTRIBUTOR_ID INT,
@DATEFROM 	VARCHAR(50),
@DATETO		VARCHAR(50),
@DEFAULT_PROMPT	VARCHAR(50),
@REPORT_TYPE VARCHAR(50),
@REPORT_FORMAT VARCHAR(50)

AS
BEGIN

	DECLARE @SQLSTRING 		NVARCHAR(4000)
	DECLARE @PARAMDEFINFTION	NVARCHAR(500)

	CREATE TABLE #SALE_RESULT_TABLE ( [DIST_SALESORDER_ID] INT, [SALES ORDER NO] VARCHAR(50), [PO NO] VARCHAR(50),[DISTRIBUTOR ID] INT,
					[Distributor Name] VARCHAR(150),[Customer Name] VARCHAR(150),[SALES Person] VARCHAR(50), [SaleOrder Date] DATETIME,
					[Distributor LineID] INT, [Distributor LineNo] INT, [Product] VARCHAR(50),[Requested Qty] INT, [Requested Date] DATETIME, 
					[Qty Shipped] INT,[Ship Date] DATETIME, [Unit Cost] NUMERIC(18,4), [Extend Cost] NUMERIC(18,4) ,[Schedule Date] DATETIME,
					[ReSale Price] NUMERIC(18,4), [Extend ReSale Price] NUMERIC(18,4), [STATUS] VARCHAR(10), [User_Defined_Field1] VARCHAR(50),
					[User_Defined_Field2] VARCHAR(50) )

	CREATE TABLE #INV_RESULT_TABLE ( [Distributor Name] VARCHAR(150), [SALES ORDER NO] VARCHAR(50), [Unit Cost] NUMERIC(18,4), [Product] VARCHAR(50), [Lot Number] VARCHAR(50), 
					[Date In] DATETIME, [Date Code] VARCHAR(20), [Available Qty] INT )

	IF @REPORT_TYPE = 'SalesReport'	
	BEGIN
		SET @SQLSTRING = N' INSERT INTO #SALE_RESULT_TABLE SELECT 
				DSO.DIST_SALESORDER_ID,DSO.DIST_SALESORDER_NO,DSO.CUSTOMER_PO,DSO.DISTRIBUTOR_ID,
				(SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = DSO.DISTRIBUTOR_ID ) AS Distributor_NAME,
				(SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = DSO.CUSTOMER_ID ) AS CUSTOMER_NAME,
				(SELECT C_SALES_PERSON FROM CUSTOMER WHERE CUSTOMER_ID = DSO.CUSTOMER_ID ) AS SALES_PERSON,
				DSO.SALESORDER_DATE,DLIT.DIST_LINEITEM_ID,DLIT.DIST_LINEITEM_NO, 
				(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = DLIT.PRODUCT_ID) AS PRODUCT,
				DLIT.DIST_REQ_QTY,DLIT.DIST_REQ_DATE,DLIT.DIST_SHIP_QTY,DLIT.DIST_SHIP_DATE,DLIT.UNIT_COST,DLIT.EXT_COST,NULL AS SCHEDULEDATE,
				DLIT.RESALE_PRICE,DLIT.EXT_RESALE_PRICE, DSO.STATUS,DLIT.User_defined_Field1,DLIT.User_defined_Field2
			   FROM 
				 (DIST_SALES_ORDER DSO INNER JOIN DIST_LINE_ITEM_TABLE DLIT ON
					  DSO.DIST_SALESORDER_ID = DLIT.DIST_SALESORDER_ID)'

		SET @PARAMDEFINFTION = N'@PRM_DISTRIBUTOR_ID INT,@PRM_DATEFROM VARCHAR(50),@PRM_DATETO VARCHAR(50)'

		IF ( 1=1)
		BEGIN
			SET @SQLSTRING = @SQLSTRING + 'WHERE 1=1 '
		END
		IF @DISTRIBUTOR_ID <> 0
		BEGIN
			SET @SQLSTRING = @SQLSTRING + '  and DSO.DISTRIBUTOR_ID = @PRM_DISTRIBUTOR_ID '
		END

		IF @DATEFROM <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' and DLIT.DIST_SHIP_DATE >= CONVERT( VARCHAR, @PRM_DATEFROM, 101) +'' 12:00AM'' '
		END

		IF @DATETO <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' and DLIT.DIST_SHIP_DATE <= CONVERT( VARCHAR, @PRM_DATETO, 101) +'' 11:59PM'' '
		END

		--PRINT '@SQLSTRING :-'+@SQLSTRING
		EXECUTE SP_EXECUTESQL @SQLSTRING, @PARAMDEFINFTION, @PRM_DISTRIBUTOR_ID = @DISTRIBUTOR_ID,@PRM_DATEFROM = @DATEFROM,@PRM_DATETO = @DATETO
	END
	ELSE
	BEGIN
		SET @SQLSTRING = N' INSERT INTO #INV_RESULT_TABLE 
					SELECT  (SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = DIL.CUSTOMER_ID), 
					(SELECT SALESORDER_NO  FROM SALES_ORDER WHERE SALESORDER_ID = ( SELECT SALESORDER_ID FROM Customer_Shipping_table Where Customershipping_id = ( select Customershipping_id FROM Customer_shipping_Line_Item_table WHERE CSLIT_ID = DIL.CSLIT_ID ) ) ),
					(SELECT TOP 1 COOL_UNIT_COST FROM Customer_Open_Order_LineItem WHERE SALESORDER_ID = ( SELECT SALESORDER_ID FROM Customer_Shipping_table Where 
							Customershipping_id = ( select Customershipping_id FROM Customer_shipping_Line_Item_table WHERE CSLIT_ID = DIL.CSLIT_ID ) ) ),
					PART_NUMBER, LOT_NUMBER, Date_In, Date_code, ( ISNULL(sum(QTY_IN),0)-ISNULL( sum(SHIPPED_QTY), 0 ) )
					FROM DISTRIBUTOR_INVENTORY_LOT DIL, PRODUCT P 
					WHERE DIL.ProcessStep_id = P.ProcessStep_Id AND QTY_IN > 0 '

		IF @DISTRIBUTOR_ID <> 0
		BEGIN
			SET @SQLSTRING = @SQLSTRING + '  and DIL.CUSTOMER_ID = ' + @DISTRIBUTOR_ID
		END
		
		SET @SQLSTRING = @SQLSTRING + ' GROUP BY CUSTOMER_ID,CSLIT_ID,PART_NUMBER, LOT_NUMBER, DATE_IN, DATE_CODE'

		--PRINT @SQLSTRING				
		EXEC (@SQLSTRING)
	END
	
	IF @REPORT_FORMAT = 'XLREPORT'
	BEGIN
		IF @REPORT_TYPE = 'SalesReport'	
		BEGIN
			SELECT [SALES ORDER NO], [User_defined_Field1] AS [DW ID], [PO NO], [Distributor Name],[Customer Name],[Sales Person], [SaleOrder Date],
					[Distributor LineNo],[Product],[Requested Qty], [Requested Date], 
					[Qty Shipped],[Ship Date], [Unit Cost], [Extend Cost],[ReSale Price], [Extend ReSale Price], [Schedule Date],[Status]
				FROM #SALE_RESULT_TABLE 
		END
		ELSE
		BEGIN
			SELECT * FROM #INV_RESULT_TABLE WHERE [Available Qty] > 0
		END
	END
	ELSE
	BEGIN
		SELECT * FROM #RESULT_TABLE
	END
END
